#!/usr/bin/env python
# -*- coding: utf-8 -*-

from utils import log
from sqlalchemy.orm import mapper, sessionmaker
from datetime import datetime
from sqlalchemy import Table, MetaData, Column, ForeignKey, Integer, BigInteger, String, Unicode, UnicodeText, DateTime
from controls.data_pool_mysql import engine

# 创建到数据库的连接,echo=True表示用logging输出调试结果
metadata = MetaData()
Session = sessionmaker()  # 创建了一个自定义了的 Session类
Session.configure(bind=engine)  # 将创建的数据库连接关联到这个session

"""user_ss collection has
   ssid:       用户设备的ssid (str)
   userid:     用户的userid (int)
   ctime:      注册时间(datetime)
   ltime:      最后一次登录 (datetime)
"""


class UserSsItem(object):  # 创建一个映射类

    def to_dict(self):
        res = {}
        res['userid'] = self.userid
        res['ssid'] = self.ssid
        res['ctime'] = self.ctime
        res['ltime'] = self.ltime
        return res

    def __repr__(self):
        return "%d" % (self.userid)


user_ss_table = Table(
    'user_ss', metadata,
    Column('ssid', Unicode(65), primary_key=True),
    Column('userid', Integer, index=True, nullable=False),
    Column('ctime', DateTime, default=datetime.now, nullable=False),
    Column('ltime', DateTime),
)

mapper(UserSsItem, user_ss_table)  # 把表映射到类


def create_user_ss_table_if_not_exist():
    metadata.create_all(engine)  # 在数据库中生成表


def update_userid_by_ssid(p_item):
    item = UserSsItem()
    item.ssid = p_item['ssid']
    item.userid = p_item['userid']
    item.ltime = datetime.now()
    try:
        session = Session()
        session.merge(item)  # 往session中添加内容
        session.flush()  # 保存数据
        session.commit()  # 数据库事务的提交,sisson自动过期而不需要关闭
        return True
    except Exception as e:
        log.exp(e)
        session.rollback()
        return False
    finally:
        session.close()
        # session.remove()


def get_ssid_by_userid(p_userid):
    try:
        session = Session()
        items = session.query(UserSsItem).filter_by(userid=p_userid).all()
        res = []
        for item in items:
            res.append(item.ssid)
        return res
    except Exception as e:
        log.exp(e)
        return None
    finally:
        session.close()
        # session.remove()


if __name__ == "__main__":
    create_user_ss_table_if_not_exist()
    item1 = {'ssid': 'abc', 'userid': 1234}
    item2 = {'ssid': 'def', 'userid': 1234}
    item3 = {'ssid': 'ghi', 'userid': 5678}
    item4 = {'ssid': 'jkl', 'userid': 1234}
    item5 = {'ssid': 'ghi', 'userid': 6789}
    update_userid_by_ssid(item1)
    update_userid_by_ssid(item2)
    update_userid_by_ssid(item3)
    update_userid_by_ssid(item4)
    update_userid_by_ssid(item5)
    print get_ssid_by_userid(1234)
